package com.ys.service.business.impl;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.ys.entity.amazonorder.TpAmazonShopOrderDaily;
import com.ys.entity.business.TpAmazonShopProduct;
import com.ys.entity.business.TpAmazonShopProductCount;
import com.ys.entity.business.TpAmazonShopProductReport;
import com.ys.mapper.business.DiySqlMapper;
import com.ys.service.business.TpAmazonShopProductCountService;
import com.ys.vo.R;

@Service("tpAmazonShopProductCountService")
public class TpAmazonShopProductCountServiceImpl implements TpAmazonShopProductCountService {
    private static final Logger logger = LoggerFactory.getLogger(TpAmazonShopProductCountServiceImpl.class);

	@Autowired
	private DiySqlMapper diySqlMapper;

	
	@Override
	public R add(List<TpAmazonShopProductCount> list) {

		diySqlMapper.insertAmazonShopProductCount(list);
		return R.ok("操作成功");
	}

	@Override
	public void deleteAmazonShopProductCount() {
		//获取30天以前的时间日期
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.DAY_OF_MONTH, -30);
		String agoDay = sdf.format(calendar.getTime());
		//删除
		Map<String, Object> params = new HashMap<>();
		params.put("infoTime",agoDay);
		TpAmazonShopProductCount entity = JSON.parseObject(JSON.toJSONString(params), TpAmazonShopProductCount.class);

		diySqlMapper.deleteAmazonShopProductCount(entity);
	}

	@Override
	public R addCountAll(List<TpAmazonShopProductCount> list) {

		diySqlMapper.insertAmazonShopProductCountAll(list);

		return R.ok("操作成功");
	}

	/**
	 * 批量修改运营分析表数据
	 */
	@Override
	public R updateAmazonShopProductCount(List<Map<String, Object>> list) {

		diySqlMapper.updateAmazonShopProductCount(list);
		return R.ok("操作成功");
	}

	/**
	 * 批量修改运营分析表-全数据
	 */
	@Override
	public R updateAmazonShopProductCountAll(List<Map<String, Object>> list) {

		diySqlMapper.updateAmazonShopProductCountAll(list);
		return R.ok("操作成功");
	}

	@Override
	public void deleteAmazonShopProductCountYesterDay(Map<String, Object> params) {

		TpAmazonShopProductCount entity = JSON.parseObject(JSON.toJSONString(params), TpAmazonShopProductCount.class);

		diySqlMapper.deleteAmazonShopProductCountYesterDay(entity);
	}


	@Override
	public void updateTpAmazonShopOrderDailyByDay(String agoDay) {
		//获取3天前时间日期
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.DAY_OF_MONTH, -2);
		String agoDayNum = sdf.format(calendar.getTime());

		Calendar calendar1 = Calendar.getInstance();
		try {
			calendar1.setTime(sdf.parse(agoDay));
		} catch (ParseException e) {
			e.printStackTrace();
		}
		//calendar1.add(Calendar.DAY_OF_MONTH, -1);
		String agoDayNumByReport = sdf.format(calendar1.getTime());

		List<Map<String, Object>> resProductList = new TpAmazonShopProduct()
				.addField("B.name AS productName")
				.addField("C.VGsJc AS vGsJc")
				.addField("C.VGsBm AS vGsBm")
				.addField("S.ShopName AS shopName")
				.addField("U.UserName AS userName")
				.addField("U.userId AS userId")
				.addField("CONVERT ( DECIMAL ( 18, 2 ), N.monthlySales/ 3 ) AS ninetyMonthCount")//统计90天的月销量
				.addField("CONVERT ( DECIMAL ( 18, 2 ), T.monthlySales ) AS thirtyMonthCount")//统计30天的月销量
				.addLeftJoin("(SELECT ShopId,Asin,SellerSku,SUM ( Num ) AS monthlySales " +
						"FROM kjecp_report.dbo.TP_AmazonShopOrderDaily WHERE DateDiff( dd, DayPurchase, getdate( ) ) <= 90  " +
						"GROUP BY ShopId,Asin,SellerSku ) N ON N.sellerSku = A.sellerSku AND N.Asin = A.Asin AND N.ShopId = A.ShopId")
				.addLeftJoin("(SELECT ShopId,Asin,SellerSku,SUM ( Num ) AS monthlySales " +
						"FROM kjecp_report.dbo.TP_AmazonShopOrderDaily WHERE DateDiff( dd, DayPurchase, getdate( ) ) <= 30 " +
						"GROUP BY ShopId,Asin,SellerSku ) T ON T.sellerSku = A.sellerSku AND T.Asin = A.Asin AND T.ShopId = A.ShopId")
				.addLeftJoin("kjecp.dbo.TP_ProductBasis B ON A.code = B.code")
				.addLeftJoin("kjecp.dbo.TP_AmazonShop S ON A.ShopId = S.Id")
				.addLeftJoin("kjecp.dbo.TP_AmazonSeller SR ON SR.id = S.amazonSellerId")
				.addLeftJoin("kjecp.dbo.THR_OperateCompanyApply C ON SR.Vgsbm = C.VGsBm")
				.addLeftJoin("kjecp.dbo.TS_UserChange U ON C.OperatorChangeId = U.Id")
				.where("A.code IS NOT NULL")
				.queryMap();
		
        logger.info("定时修改运营跟踪表数据：查询记录条数："+resProductList.size());

		List<Map<String, Object>> resReportList = new ArrayList<>();

		if(agoDay.compareTo(agoDayNum)>=0){
			resReportList = new TpAmazonShopProductReport().where("A.DataDate = '" + agoDayNumByReport + "'").queryMap();
		}else {
			resReportList = new TpAmazonShopProductReport().setMainTable("(SELECT * FROM kjecp_amazonproduct.dbo.TP_AmazonShopProductReportAll Where DataDate = '" + agoDayNumByReport + "')").queryMap();
		}

		//获取对应日期的商品日报表的数据
		List<TpAmazonShopOrderDaily> orderDailyList = new TpAmazonShopOrderDaily()
				.where("A.DayPurchase = '"+ agoDay +"'")
				.queryList();

		//查询采购未入库
		List<Map<String, Object>> noStorageCountList = diySqlMapper.searchNoStorageCountByAllShopCode();
		//查询仓储库存，根据商品code
		List<Map<String, Object>> stockControlList = diySqlMapper.searchStockControlByAllCode();


		if(resProductList!=null && resProductList.size()>0){

			List<TpAmazonShopProductCount> addlist = new ArrayList<>();
			//获取数据
			for(Map<String, Object> item:resProductList){

				String shopProductGuid = item.get("guid").toString() ;
				String shopProductId = item.get("id").toString() ;
				List<Map<String, Object>> resReportListByProduct = resReportList.stream()
						.filter(reportItem->reportItem.get("shopProductId").toString().equals(shopProductId))
						.collect(Collectors.toList());

				//需要另外统计的
				//数量
				String count = "0";
				List<TpAmazonShopOrderDaily> orderDailyListByPro = orderDailyList.stream()
						.filter(countItem->countItem.getAsin().equals(item.get("asin")) && countItem.getSellerSku().equals(item.get("sellerSku"))
								&& countItem.getShopId().equals(item.get("shopId")))
						.collect(Collectors.toList());
				if (orderDailyListByPro.size()>0) {
					count = orderDailyListByPro.get(0).getNum().toString();
				}
				//采购未入库数量
				String noStorageCount = "0";
				List<Map<String, Object>> noStorageCountListByCode = noStorageCountList.stream()
						.filter(countItem->countItem.get("SpBm").toString().equals(item.get("code")) && countItem.get("VGsBm").toString().equals(item.get("vGsBm")) )
						.collect(Collectors.toList());
				if (noStorageCountListByCode.size()>0 && noStorageCountListByCode.get(0).get("noStorageCount")!=null) {
					noStorageCount = noStorageCountListByCode.get(0).get("noStorageCount").toString();
				}
				//仓储库存数量
				String inboundWorkingQuantity = "0";
				List<Map<String, Object>> stockControlListByCode = stockControlList.stream()
						.filter(countItem->countItem.get("ProductCode").toString().equals(item.get("code")) && countItem.get("VGsBm").toString().equals(item.get("vGsBm")) )
						.collect(Collectors.toList());
				if (stockControlListByCode.size()>0) {
					inboundWorkingQuantity = stockControlListByCode.get(0).get("productNumAll").toString();
				}

				String visit = "0";
				String recommendPrice = "0";
				String commoditySession = "0";
				String rank = "0";
				String price = "0";
				if(resReportListByProduct!=null && resReportListByProduct.size()>0){
					Map<String, Object> reportItem = resReportListByProduct.get(0);
					//访问量
					visit = (reportItem.get("sessionsNum")!=null?reportItem.get("sessionsNum").toString():"0");
					//购买赢得率
					BigDecimal recommendPriceCount =reportItem.get("recommendPrice")!=null? (BigDecimal) reportItem.get("recommendPrice"):BigDecimal.ZERO;
					recommendPrice = recommendPriceCount.toString();
					//转换率
					BigDecimal commoditySessionCount = reportItem.get("commoditySession")!=null?(BigDecimal) reportItem.get("commoditySession"):BigDecimal.ZERO;
					commoditySession = commoditySessionCount.toString();
					//排名
					rank = (reportItem.get("salesRank")!=null?reportItem.get("salesRank").toString():"0");
					//价格
					price =(reportItem.get("listingPrice")!=null?reportItem.get("listingPrice").toString():"0");
				}

				//统计90天的月销量
				String ninetyMonthCount = (item.get("ninetyMonthCount")!=null?item.get("ninetyMonthCount").toString():"0");
				//统计30天的月销量
				String thirtyMonthCount = (item.get("thirtyMonthCount")!=null?item.get("thirtyMonthCount").toString():"0");

				//info填入
				JSONObject infoJson = new JSONObject();
				infoJson.put("rank",rank);
				infoJson.put("price",price);
				infoJson.put("count",count);
				infoJson.put("visit",visit);
				infoJson.put("recommendPrice",recommendPrice);
				infoJson.put("commoditySession",commoditySession);
				infoJson.put("ninetyMonthCount",ninetyMonthCount);
				infoJson.put("thirtyMonthCount",thirtyMonthCount);
				infoJson.put("noStorageCount",noStorageCount);
				infoJson.put("inboundWorkingQuantity",inboundWorkingQuantity);

				//字段装入
				Map<String, Object> params = new HashMap<>();
				params.put("shopProductGuid",shopProductGuid);
				params.put("shopProductId",shopProductId);
				params.put("infoTime",agoDay);
				params.put("info",infoJson.toString());
				params.put("productName",item.get("productName"));
				params.put("vGsJc",item.get("vGsJc"));
				params.put("shopName",item.get("shopName"));
				params.put("userName",item.get("userName"));
				params.put("vGsBm",item.get("vGsBm"));
				params.put("userId",item.get("userId"));
				params.put("status",1);

				TpAmazonShopProductCount entity = JSON.parseObject(JSON.toJSONString(params), TpAmazonShopProductCount.class);
				addlist.add(entity);
				//添加
				//addlist.add(params);

			}
			//删除
			Map<String, Object> paramsDel = new HashMap<>();
			paramsDel.put("infoTime",agoDay);
			TpAmazonShopProductCount entity = JSON.parseObject(JSON.toJSONString(paramsDel), TpAmazonShopProductCount.class);
			diySqlMapper.deleteAmazonShopProductCountYesterDay(entity);
			//tpAmazonShopProductCountService.deleteAmazonShopProductCountYesterDay(paramsDel);
			//因为该服务器支持最多 2100 个参数。请减少参数的数目，然后重新发送该请求。
			//所以分批插入,一次插入100
			for(int i=0;i<addlist.size()+100;i=i+100){
				//List<Map<String, Object>> newlist = addlist.stream().skip(i).limit(500).collect(Collectors.toList());
				//批量修改
				List<TpAmazonShopProductCount> newlist = addlist.stream().skip(i).limit(100).collect(Collectors.toList());
				diySqlMapper.insertAmazonShopProductCount(newlist);
				//diySqlMapper.insertAmazonShopProductCountAll(newlist);
				//tpAmazonShopProductCountService.add(newlist);
				//tpAmazonShopProductCountService.addCountAll(newlist);
			}

		}

	}

	public void addTpAmazonShopOrderDaily(){
		//获取昨天时间日期
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.DAY_OF_MONTH, -1);
		String agoDay = sdf.format(calendar.getTime());

		List<Map<String, Object>> resList = new TpAmazonShopProduct()
				.addField("B.SalesRank AS salesRankCount")//排名
				.addField("B.SessionsNum AS sessionsCount")//会话次数,访问量
				.addField("B.ListingPrice AS listingPriceCount")//价格
				.addField("B.RecommendPrice AS recommendPriceCount")//推荐报价百分比,购买赢得率
				.addField("B.CommoditySession AS commoditySessionCount")//商品会话百分比,转换率
				.addField("CONVERT ( DECIMAL ( 18, 2 ), N.monthlySales/ 3 ) AS ninetyMonthCount")//统计90天的月销量
				.addField("CONVERT ( DECIMAL ( 18, 2 ), T.monthlySales ) AS thirtyMonthCount")//统计30天的月销量
				.addLeftJoin("(SELECT " +
						"C.SalesRank, " +
						"C.SessionsNum, " +
						"C.ListingPrice, " +
						"C.DataDate, " +
						"C.RecommendPrice, " +
						"C.CommoditySession, " +
						"C.ShopProductGuid  " +
						"FROM " +
						"kjecp_amazonproduct.dbo.TP_AmazonShopProductReport C " +
						"INNER JOIN ( SELECT MAX (D.DataDate) AS DataDate, D.ShopProductGuid FROM kjecp_amazonproduct.dbo.TP_AmazonShopProductReport D GROUP BY D.ShopProductGuid ) AS D2 ON D2.ShopProductGuid = C.ShopProductGuid AND D2.DataDate = C.DataDate " +
						") B ON A.Guid = B.ShopProductGuid")

				//.addLeftJoin("TP_AmazonShopProductReport B ON A.Guid = B.ShopProductGuid")
				.addLeftJoin("(SELECT ShopId,Asin,SellerSku,SUM ( Num ) AS monthlySales " +
						"FROM kjecp_report.dbo.TP_AmazonShopOrderDaily WHERE DateDiff( dd, DayPurchase, getdate( ) ) <= 90  " +
						"GROUP BY ShopId,Asin,SellerSku ) N ON N.sellerSku = A.sellerSku AND N.Asin = A.Asin")
				.addLeftJoin("(SELECT ShopId,Asin,SellerSku,SUM ( Num ) AS monthlySales " +
						"FROM kjecp_report.dbo.TP_AmazonShopOrderDaily WHERE DateDiff( dd, DayPurchase, getdate( ) ) <= 30 " +
						"GROUP BY ShopId,Asin,SellerSku ) T ON T.sellerSku = A.sellerSku AND T.Asin = A.Asin")
				.where("A.code IS NOT NULL")
				//.where("B.DataDate = '"+agoDay+"'")
				.setOrderby("A.code asc")

				.queryMap();


		if(resList!=null && resList.size()>0){
			List<TpAmazonShopProductCount> addlist = new ArrayList<>();
			//获取数据
			resList.forEach(item->{
				String shopProductGuid = item.get("guid").toString();//商品表guid

				//数量=》可售数量
				String count = (item.get("fulfillableQuantity")!=null?item.get("fulfillableQuantity").toString():"0");
				//排名
				String rank = (item.get("salesRankCount")!=null?item.get("salesRankCount").toString():"0");
				//价格
				String price =(item.get("listingPriceCount")!=null?item.get("listingPriceCount").toString():"0");
				//访问量
				String visit = (item.get("sessionsCount")!=null?item.get("sessionsCount").toString():"0");
				//购买赢得率
				String recommendPrice = (item.get("recommendPriceCount")!=null?item.get("recommendPriceCount").toString():"0");
				//转换率
				String commoditySession = (item.get("commoditySessionCount")!=null?item.get("commoditySessionCount").toString():"0");
				//统计90天的月销量
				String ninetyMonthCount = (item.get("ninetyMonthCount")!=null?item.get("ninetyMonthCount").toString():"0");
				//统计30天的月销量
				String thirtyMonthCount = (item.get("thirtyMonthCount")!=null?item.get("thirtyMonthCount").toString():"0");

				//info填入
				JSONObject infoJson = new JSONObject();
				infoJson.put("rank",rank);
				infoJson.put("price",price);
				infoJson.put("count",count);
				infoJson.put("visit",visit);
				infoJson.put("recommendPrice",recommendPrice);
				infoJson.put("commoditySession",commoditySession);
				infoJson.put("ninetyMonthCount",ninetyMonthCount);
				infoJson.put("thirtyMonthCount",thirtyMonthCount);

				//字段填入
				Map<String, Object> params = new HashMap<>();
				params.put("shopProductGuid",shopProductGuid);
				params.put("status",0);
				params.put("info",infoJson);
				params.put("infoTime",agoDay);

				TpAmazonShopProductCount entity = JSON.parseObject(JSON.toJSONString(params), TpAmazonShopProductCount.class);
				//添加
				addlist.add(entity);

			});

			//因为该服务器支持最多 2100 个参数。请减少参数的数目，然后重新发送该请求。
			//所以分批插入,一次插入500
			for(int i=0;i<addlist.size()+500;i=i+500){
				List<TpAmazonShopProductCount> newlist = addlist.stream().skip(i).limit(500).collect(Collectors.toList());
				diySqlMapper.insertAmazonShopProductCount(newlist);
				diySqlMapper.insertAmazonShopProductCountAll(newlist);
				//tpAmazonShopProductCountService.add(newlist);
				//tpAmazonShopProductCountService.addCountAll(newlist);
			}
		}



	}

}
